package ms07.ticketPro;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class Database {

	private static final String DATABASE_NAME = "example.db";
	private static final int DATABASE_VERSION = 3;

	private Context context;
	private SQLiteDatabase db;

	private SQLiteStatement insertStmtTicket;
	private SQLiteStatement insertStmtStation;
	private static final String INSERT_TICKET = "INSERT INTO tickets (ticket_id, organisation, price, valid_from, valid_till, city_from, city_to, adults, children, memberCard, seatclass, username, validation) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private static final String INSERT_STATION = "INSERT INTO stations (name, sms) VALUES (?,?)";

	public Database(Context context) {
		DebugLog.getInstance().log("database object created!");
		
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();
		this.insertStmtTicket = this.db.compileStatement(INSERT_TICKET);
		this.insertStmtStation = this.db.compileStatement(INSERT_STATION);
	}

	public long insertStation(String name, String sms){
		DebugLog.getInstance().log("insert a new station");

		this.insertStmtTicket.bindString(1, name);
		this.insertStmtTicket.bindString(2, sms);
		
		return this.insertStmtStation.executeInsert();
	}
	
	public boolean deleteTicket(Ticket ticket){
		int deleted_rows = this.db.delete("tickets", "id="+ticket.getInternalId(), null);
		
		return (deleted_rows==1);
	}
	
	public long insertTicket(Ticket ticket) {
		DebugLog.getInstance().log("insert a new  ticket");
		/*
		 * id, 
		 * price, 
		 * valid_from, 
		 * valid_till, 
		 * city_from, 
		 * city_to, 
		 * adults, 
		 * children, 
		 * memberCard, 
		 * seatclass, 
		 * username, 
		 * validation
		 */
		
		DebugLog.getInstance().log("TICKET OBJECT: "+ticket.toString());

		int i = 1;
		DebugLog.getInstance().log("do id");
		this.insertStmtTicket.bindLong(i++, ticket.getId());
		DebugLog.getInstance().log("do organisation");
		this.insertStmtTicket.bindString(i++, ticket.getOrganisation());
		DebugLog.getInstance().log("do price");
		this.insertStmtTicket.bindDouble(i++, ticket.getPrice());
		DebugLog.getInstance().log("do getValid_from");
		this.insertStmtTicket.bindLong(i++, ticket.getValid_from()!=null?ticket.getValid_from().getTime():0);
		DebugLog.getInstance().log("do getValid_till");
		this.insertStmtTicket.bindLong(i++, ticket.getValid_till()!=null?ticket.getValid_till().getTime():0);
		DebugLog.getInstance().log("do getCity_from");
		this.insertStmtTicket.bindString(i++, ticket.getCity_from());
		DebugLog.getInstance().log("do getCity_to");
		this.insertStmtTicket.bindString(i++, ticket.getCity_to());
		DebugLog.getInstance().log("do getAdults");
		this.insertStmtTicket.bindLong(i++, ticket.getAdults());
		DebugLog.getInstance().log("do getChildren");
		this.insertStmtTicket.bindLong(i++, ticket.getChildren());
		DebugLog.getInstance().log("do getMemberCard");
		this.insertStmtTicket.bindString(i++, ticket.getMemberCard());
		DebugLog.getInstance().log("do getSeatclass");
		this.insertStmtTicket.bindString(i++, ticket.getSeatclass());
		DebugLog.getInstance().log("do getUsername");
		this.insertStmtTicket.bindString(i++, ticket.getUsername());
		DebugLog.getInstance().log("do validation");
		StringBuilder validation = new StringBuilder();
		for(int k=0; k<ticket.getValidation().size(); k++){
			if(k>0) validation.append(",");
			validation.append(ticket.getValidation().get(k));
		}
		this.insertStmtTicket.bindString(i++, validation.toString());
		
		DebugLog.getInstance().log("execute insert");
		return this.insertStmtTicket.executeInsert();
	}

	public void deleteAllTickets() {
		this.db.delete("tickets", null, null);
	}

	public List<Ticket> selectAllTickets() {
		List<Ticket> list = new ArrayList<Ticket>();
		Cursor cursor = this.db.query(
							"tickets", 
							new String[] { 
								"id",
								"ticket_id", 
								"organisation",
								"price",
								"valid_from",
								"valid_till",
								"city_from",
								"city_to",
								"adults",
								"children",
								"memberCard",
								"seatclass",
								"username",
								"validation"
							}, 
							null, 
							null, 
							null, 
							null, 
							"valid_till desc"
						);
		
		
		if (cursor.moveToFirst()) {
			do{
				//create Ticket object!!!
				Ticket ticket = Ticket.getTicketInstance(cursor);
				list.add(ticket); 
			}while(cursor.moveToNext());
		}
		
		if(cursor!=null && !cursor.isClosed()){
			cursor.close();
		}
		return list;
	}

	private static class OpenHelper extends SQLiteOpenHelper {

		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE tickets ("+
								"id INTEGER PRIMARY KEY, "+
								"ticket_id INTEGER, "+
								"organisation TEXT, "+
								"price DOUBLE, "+
								"valid_from LONG, "+
								"valid_till LONG, "+
								"city_from TEXT, "+
								"city_to TEXT, "+
								"adults INTEGER, "+
								"children INTEGER, "+
								"memberCard TEXT, "+
								"seatclass TEXT, "+
								"username TEXT, "+
								"validation TEXT)");
			db.execSQL("CREATE TABLE stations (name TEXT PRIMARY KEY, sms TEXT)");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			DebugLog.getInstance().log("Upgrading database");
			
			for(int i=oldVersion; i<newVersion; i++){
				DebugLog.getInstance().log("do upgrade from Version "+i+" to Version "+(i+1));
				switch(i){
					case 1: //update from 1 -> 2 (once it's needed)
						db.execSQL("ALTER TABLE stations ADD organisation TEXT");
				}
			}
		}
	}
}
